// HSS.Data.SqlParameterCache.cs
// ----------------------------------------------------------------------------
// Licensed under the MIT license
// http://www.opensource.org/licenses/mit-license.html
// ----------------------------------------------------------------------------
// HighSpeed-Solutions, LLC
// Copyright (c) 2001-2010
// ----------------------------------------------------------------------------
// File:       SqlParameterCache.cs
// Author:     HSS\gbanta
// Created:    08/12/2010
// Modified:   12/04/2010
// ----------------------------------------------------------------------------
namespace HSS.Data
{
	#region Using Directives
	using System;
	using System.Collections;
	using System.Data;
	using System.Data.SqlClient;
	#endregion

	#region SqlParameterCache
	/// <summary>
	/// Summary description for SqlParameterCache.
	/// </summary>
	public static class SqlParameterCache
	{
		#region Fields
		private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
		#endregion

		#region Methods

		/// <summary>
		/// Clears the internal Parameter Cache. Will clear for all database and all stored procedures.
		/// </summary>
		public static void Clear()
		{
			SqlParameterCache.paramCache.Clear();
		}
		/// <summary>
		/// Removes a Cached Parameter Set if found
		/// </summary>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="storedProcedureName">the stored procedure name</param>
		/// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter is included</param>
		public static void Remove(string connectionString, string storedProcedureName, bool includeReturnValueParameter)
		{
			string hashKey = CreateHashKey(connectionString, storedProcedureName, includeReturnValueParameter);
			if (paramCache.Contains(hashKey))
			{
				paramCache.Remove(hashKey);
			}
		}

		/// <summary>
		/// Add SqlCommand Parameters array to the cache
		/// </summary>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="storedProcedureName">the stored procedure name</param>
		/// <param name="commandParameters">an array of SqlParameters to be cached</param>
		public static void CacheParameterSet(string connectionString, string storedProcedureName, params SqlParameter[] commandParameters)
		{
			CacheParameterSet(connectionString, storedProcedureName, false, commandParameters);
		}
		/// <summary>
		/// Add SqlCommand Parameters array to the cache
		/// </summary>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="storedProcedureName">the stored procedure name</param>
		/// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter is included</param>
		/// <param name="commandParameters">an array of SqlParameters to be cached</param>
		public static void CacheParameterSet(string connectionString, string storedProcedureName, bool includeReturnValueParameter, params SqlParameter[] commandParameters)
		{
			string hashKey = CreateHashKey(connectionString, storedProcedureName, includeReturnValueParameter);

			paramCache[hashKey] = commandParameters;
		}

		/// <summary>
		/// Retrieve a parameter array from the cache
		/// </summary>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="storedProcedureName">the stored procedure name</param>
		/// <returns>an array of SqlParameters</returns>
		public static SqlParameter[] GetCachedParameterSet(string connectionString, string storedProcedureName)
		{
			return GetCachedParameterSet(connectionString, storedProcedureName, false);
		}
		/// <summary>
		/// Retrieve a parameter array from the cache
		/// </summary>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="storedProcedureName">the stored procedure name</param>
		/// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param>
		/// <returns>an array of SqlParameters or NULL</returns>
		public static SqlParameter[] GetCachedParameterSet(string connectionString, string storedProcedureName, bool includeReturnValueParameter)
		{
			string hashKey = CreateHashKey(connectionString, storedProcedureName, includeReturnValueParameter);

			SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];

			if (cachedParameters == null)
			{
				return null;
			}
			else
			{
				return CloneParameters(cachedParameters);
			}
		}

		/// <summary>
		/// Retrieves the set of SqlParameters appropriate for the stored procedure
		/// </summary>
		/// <remarks>
		/// This method will query the database for this information, and then store it in a cache for future requests.
		/// </remarks>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="storedProcedureName">the name of the stored procedure</param>
		/// <returns>an array of SqlParameters</returns>
		public static SqlParameter[] GetSpParameterSet(string connectionString, string storedProcedureName)
		{
			return GetSpParameterSet(connectionString, storedProcedureName, false);
		}
		/// <summary>
		/// Retrieves the set of SqlParameters appropriate for the stored procedure
		/// </summary>
		/// <remarks>
		/// This method will query the database for this information, and then store it in a cache for future requests.
		/// </remarks>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="storedProcedureName">the name of the stored procedure</param>
		/// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param>
		/// <returns>an array of SqlParameters</returns>
		public static SqlParameter[] GetSpParameterSet(string connectionString, string storedProcedureName, bool includeReturnValueParameter)
		{
			string hashKey = CreateHashKey(connectionString, storedProcedureName, includeReturnValueParameter);
			SqlParameter[] cachedParameters;

			cachedParameters = (SqlParameter[])paramCache[hashKey];

			if (cachedParameters == null)
			{
				cachedParameters = (SqlParameter[])(paramCache[hashKey] =
					DiscoverSpParameterSet(connectionString, storedProcedureName, includeReturnValueParameter));
			}

			return CloneParameters(cachedParameters);
		}


		private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string storedProcedureName, bool includeReturnValueParameter)
		{
			using (SqlConnection cn = new SqlConnection(connectionString))
			{
				using (SqlCommand SqlCmd = new SqlCommand(storedProcedureName, cn))
				{
					SqlCmd.CommandType = CommandType.StoredProcedure;

					cn.Open();
					SqlCommandBuilder.DeriveParameters(SqlCmd);
					cn.Close();

					if (!includeReturnValueParameter)
					{
						SqlCmd.Parameters.RemoveAt(0);
					}

					SqlParameter[] discoveredParameters = new SqlParameter[SqlCmd.Parameters.Count];

					SqlCmd.Parameters.CopyTo(discoveredParameters, 0);

					return discoveredParameters;
				}
			}
		}
		private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
		{
			SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

			for (int i = 0, j = originalParameters.Length; i < j; i++)
			{
				clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
			}

			return clonedParameters;
		}
		private static string CreateHashKey(string connectionString, string storedProcedureName, bool includeReturnValueParameter)
		{
			return connectionString + ":" + storedProcedureName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
		}

		#endregion
	}
	#endregion
}